<?php

namespace App\Libs\Company\SheetTemplateHandler\Aggregate;

use App\Libs\Company\SheetTemplateHandler\Manager;
use App\Models\AggregateFixReport;
use App\Models\AggregateReport;
use App\Models\SheetTemplate;
use Illuminate\Support\Facades\Log;

class Template221 extends AbstractTemplate
{
    public $dataStartRow = 7;

    protected $dataEndRow = 17;

    protected $lastMonthData;

    protected $lastYearMonthData;

    public function generateAbstract()
    {
        $this->renderHeader();

        $monthData = [
            'A' => ['月计' => 0, '年度计划' => 0],
            'B' => ['月计' => 0, '年度计划' => 0],
            'C' => ['月计' => 0.0, '年度计划' => 0.0],
            'D' => ['月计' => 0.0, '年度计划' => 0.0],
        ];

        foreach ($this->task->subTasks as $subTask) {
            $cellData = $subTask->data['sheets']['sheet-01']['cellData'];

            try {
                $monthData['A']['月计'] += (int) $cellData[7][2]['v'];
                $monthData['A']['年度计划'] += (int) ($cellData[7][3]['v'] ?? 0);
                $monthData['B']['月计'] += (int) $cellData[8][2]['v'];
                $monthData['B']['年度计划'] += (int) ($cellData[8][3]['v'] ?? 0);
                $monthData['C']['月计'] += (float) $cellData[10][2]['v'];
                $monthData['C']['年度计划'] += (float) ($cellData[10][3]['v'] ?? 0);
                $monthData['D']['月计'] += (float) $cellData[11][2]['v'];
                $monthData['D']['年度计划'] += (float) ($cellData[11][3]['v'] ?? 0);
            } catch (\Throwable $th) {
                Log::error('填报表异常 id:' . $subTask->id);
                throw $th;
            }
        }

        $this->lastMonthData = $this->getLastMonthData();
        $this->lastYearMonthData = $this->getLastYearMonthData();

        $cellData = &$this->output['sheets']['sheet-01']['cellData'];
        $cellData[4][5]['v'] = "统计周期：{$this->task->year}月{$this->task->key}月";

        // 同期海外表
        $aggregateReport222 = AggregateReport::where([
            'sheet_template_id' => 222,
            'frequency' => $this->task->frequency,
            'year' => $this->task->year,
            'key' => $this->task->key,
        ])
            ->first();
        if ($aggregateReport222) {
            $cellData[8][2] = ['v' => (int) $aggregateReport222->data['sheets']['sheet-01']['cellData'][4][2]['v'], 't' => 2];
            $this->generateRow($cellData, 8, '1、接待入境游客');

            $cellData[12][2] = ['v' => floor(((float) ($cellData[8][2]['v']) ?: 0) * 1.1), 't' => 2];
            $this->generateRow($cellData, 12, '1、海外游客人天');

            /**
             * @var \App\Libs\Company\SheetTemplateHandler\Aggregate\Template222 $handler222
             */
            $handler222 = Manager::createAggregateHandler(SheetTemplate::find($aggregateReport222->sheet_template_id), $this->task);
            $handler222->updateDomestic($aggregateReport222, $monthData['A']['月计'] + $monthData['B']['月计']);
        } else {
            $cellData[8][2] = ['v' => '', 't' => 2];
            $cellData[12][2] = ['v' => '', 't' => 2];
        }

        $cellData[9][2] = ['v' => $monthData['A']['月计'] + $monthData['B']['月计'], 't' => 2];
        $cellData[9][7] = ['v' => $monthData['A']['年度计划'] + $monthData['B']['年度计划'], 't' => 2];
        $this->generateRow($cellData, 9, '2、接待国内游客');

        $cellData[10][2] = ['v' => $monthData['B']['月计'], 't' => 2];
        $cellData[10][7] = ['v' => $monthData['B']['年度计划'], 't' => 2];
        $this->generateRow($cellData, 10, '其中：一日游游客');

        $cellData[13][2] = ['v' => floor(((float) $cellData[9][2]['v']) * 1.1), 't' => 2];
        $cellData[13][7] = ['v' => 0, 't' => 2];
        $this->generateRow($cellData, 13, '2、国内游客人天');

        $cellData[16][2] = ['v' => round($monthData['C']['月计'] + $monthData['D']['月计'], 2), 't' => 2];
        $cellData[16][7] = ['v' => round($monthData['C']['年度计划'] + $monthData['D']['年度计划'], 2), 't' => 2];
        $this->generateRow($cellData, 16, '2、国内旅游收入');

        $cellData[17][2] = ['v' => round($monthData['D']['月计'], 2), 't' => 2];
        $cellData[17][7] = ['v' => round($monthData['D']['年度计划'], 2), 't' => 2];
        $this->generateRow($cellData, 17, '其中：一日游游客收入');

        if (empty($this->lastYearMonthData)) {
            // 国内外增长率
            $cellData[7][3] = ['v' => 0, 't' => 2];
            // 国内外人天增长率
            $cellData[11][3] = ['v' => 0, 't' => 2];
            // 旅游总收入增长率
            // $cellData[14][3] = ['v' => 0, 't' => 2];
        } else {
            // 国内外增长率
            $lastTotalA = (int) $this->lastYearMonthData['1、接待入境游客'][2]['v'] + (int) $this->lastYearMonthData['2、接待国内游客'][2]['v'];
            $cellData[7][3] = ['f' => "=IFERROR(((C8-{$lastTotalA})/{$lastTotalA}-MOD((C8-{$lastTotalA})/{$lastTotalA},0.0001))*100, 0)", 't' => 2];
            // 国内外人天增长率
            $lastTotalB = (int) $this->lastYearMonthData['1、海外游客人天'][2]['v'] + (int) $this->lastYearMonthData['2、国内游客人天'][2]['v'];
            $cellData[11][3] = ['f' => "=IFERROR(((C12-{$lastTotalB})/{$lastTotalB}-MOD((C12-{$lastTotalB})/{$lastTotalB},0.0001))*100, 0)", 't' => 2];
            // 旅游总收入增长率
            // $lastTotalC = (float) ($this->lastYearMonthData['1、旅游外汇收入'][2]['v'] ?? 0) + (float) $this->lastYearMonthData['2、国内旅游收入'][2]['v'];
            // $cellData[14][3] = ['f' => "=IFERROR(((C15-{$lastTotalC})/{$lastTotalC}-MOD((C15-{$lastTotalC})/{$lastTotalC},0.0001))*100, 0)", 't' => 2];
        }

        return $this->output;
    }

    public function generateRow(&$cellData, $row, $key)
    {
        if (empty($this->lastYearMonthData[$key][2]['v'])) {
            $cellData[$row][3] = ['v' => 0, 't' => 2];
        } else {
            $lastYearMonthVal = $this->lastYearMonthData[$key][2]['v'];
            $cellData[$row][3] = ['f' => "=IFERROR(((C" . ($row + 1) . "-{$lastYearMonthVal})/{$lastYearMonthVal}-MOD((C" . ($row + 1) . "-{$lastYearMonthVal})/{$lastYearMonthVal},0.0001))*100, 0)", 't' => 2];
        }
        if ($this->task->key == 1 || empty($this->lastMonthData[$key][4]['v'])) {
            $cellData[$row][4] = ['v' => $cellData[$row][2]['v'], 't' => 2];
        } else {
            $cellData[$row][4] = ['v' => $cellData[$row][2]['v'] + $this->lastMonthData[$key][4]['v'], 't' => 2];
        }
        if (empty($this->lastYearMonthData[$key][4]['v'])) {
            $cellData[$row][5] = ['v' => $cellData[$row][4]['v'], 't' => 2];
        } else {
            $cellData[$row][5] = ['v' => round($cellData[$row][4]['v'] - $this->lastYearMonthData[$key][4]['v'], 2), 't' => 2];
        }
    }

    public function getLastMonthData()
    {
        if ($this->task->key == 1) {
            $year = $this->task->year - 1;
            $key = 12;
        } else {
            $year = $this->task->year;
            $key = (int) $this->task->key - 1;
        }

        $lastAggregateReport = AggregateFixReport::where([
            'sheet_template_id' => $this->template->id,
            'frequency' => $this->task->frequency,
            'year' => $year,
            'key' => $key,
        ])
            ->first();
        if (!$lastAggregateReport) {
            $lastAggregateReport = AggregateReport::where([
                'sheet_template_id' => $this->template->id,
                'frequency' => $this->task->frequency,
                'year' => $year,
                'key' => $key,
            ])
                ->first();
            if (!$lastAggregateReport) {
                return [];
            }
        }

        $data = [];
        $i = $this->dataStartRow;
        while ($i <= $this->dataEndRow) {
            $row = $lastAggregateReport->data['sheets']['sheet-01']['cellData'][$i++];
            $data[$row[0]['v']] = $row;
        }
        return $data;
    }

    public function getLastYearMonthData()
    {
        $lastAggregateReport = AggregateFixReport::where([
            'sheet_template_id' => $this->template->id,
            'frequency' => $this->task->frequency,
            'year' => $this->task->year - 1,
            'key' => $this->task->key,
        ])
            ->first();
        if (!$lastAggregateReport) {
            $lastAggregateReport = AggregateReport::where([
                'sheet_template_id' => $this->template->id,
                'frequency' => $this->task->frequency,
                'year' => $this->task->year - 1,
                'key' => $this->task->key,
            ])
                ->first();
            if (!$lastAggregateReport) {
                return [];
            }
        }
        $data = [];
        $i = $this->dataStartRow;
        while ($i <= $this->dataEndRow) {
            $row = $lastAggregateReport->data['sheets']['sheet-01']['cellData'][$i++];
            $data[$row[0]['v']] = $row;
        }
        return $data;
    }

    public function updateOverseas(AggregateReport $aggregateReport, $value)
    {
        $this->lastMonthData = $this->getLastMonthData();
        $this->lastYearMonthData = $this->getLastYearMonthData();

        $data = $aggregateReport->data;

        $cellData = &$data['sheets']['sheet-01']['cellData'];
        $cellData[8][2] = ['v' => $value, 't' => 2];
        $cellData[8][7] = ['v' => 0, 't' => 2];
        $this->generateRow($cellData, 8, '1、接待入境游客');

        $cellData[12][2] = ['v' => floor(((float) ($cellData[8][2]['v']) ?: 0) * 1.1), 't' => 2];
        $cellData[12][7] = ['v' => 0, 't' => 2];
        $this->generateRow($cellData, 12, '1、海外游客人天');

        $aggregateReport->data = $data;
        return $aggregateReport->save();
    }
}
